package com.ldq.imooc.basics.dataOperation;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Author : 李道泉
 * @Description :
 * @Date : 2018/4/27 9:20
 * @Modified by :
 */
public class GoddessDao {

    public void addGoddess(Goddess goddess) throws SQLException {

        Connection connection = DBUtil.getConn();
        String sql = "" + "insert into imooc_goddess" +
                "(user_name,sex,age,birthday,email,mobile," +
                "create_user,create_date,update_user,update_date," +
                "isdel)values(" +
                "?,?,?,?,?,?,?,CURRENT_DATE ,?,CURRENT_DATE ,?);";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, goddess.getUser_name());
        ps.setInt(2, goddess.getSex());
        ps.setInt(3, goddess.getAge());
        ps.setDate(4, new Date(goddess.getBirthday().getTime()));
        ps.setString(5, goddess.getEmail());
        ps.setString(6, goddess.getMobile());
        ps.setString(7, goddess.getCreate_user());
        ps.setString(8, goddess.getUpdate_user());
        ps.setInt(9, goddess.getIsdel());
        ps.execute();

    }

    public void deleteGoddess(Integer id) throws SQLException {

        Connection connection = DBUtil.getConn();
        String sql = " DELETE from imooc_goddess where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, id);
        ps.execute();

    }

    public void updateGoddess(Goddess goddess) throws SQLException {

        Connection connection = DBUtil.getConn();
        String sql = " UPDATE imooc_goddess " +
                " set user_name=?, sex=?, age=?, birthday=?, email=?, mobile=?, " +
                " update_user=?, update_date=CURRENT_DATE, isdel=? " +
                " where id=?;";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, goddess.getUser_name());
        ps.setInt(2, goddess.getSex());
        ps.setInt(3, goddess.getAge());
        ps.setDate(4, new Date(goddess.getBirthday().getTime()));
        ps.setString(5, goddess.getEmail());
        ps.setString(6, goddess.getMobile());
        ps.setString(7, goddess.getUpdate_user());
        ps.setInt(8, goddess.getIsdel());
        ps.setInt(9, goddess.getId());
        ps.execute();
    }

    public List<Goddess> goddessList() throws SQLException {

        Connection connection = DBUtil.getConn();

        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select t.id,t.user_name,t.age from imooc_goddess t");

        List<Goddess> goddessList = new ArrayList<Goddess>();
        Goddess goddess = null;
        while (resultSet.next()) {

            goddess = new Goddess();
            goddess.setId(resultSet.getInt("id"));
            goddess.setUser_name(resultSet.getString("user_name"));
            goddess.setAge(resultSet.getInt("age"));
            goddessList.add(goddess);
        }
        return goddessList;
    }

    public Goddess querySingle(Integer id) throws SQLException {

        Goddess goddess = null;
        Connection connection = DBUtil.getConn();

        String sql = " select * from imooc_goddess where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {

            goddess = new Goddess();
            goddess.setId(rs.getInt("id"));
            goddess.setUser_name(rs.getString("user_name"));
            goddess.setSex(rs.getInt("sex"));
            goddess.setAge(rs.getInt("age"));
            goddess.setBirthday(rs.getDate("birthday"));
            goddess.setEmail(rs.getString("email"));
            goddess.setMobile(rs.getString("mobile"));
            goddess.setCreate_user(rs.getString("create_user"));
            goddess.setCreate_date(rs.getDate("create_date"));
            goddess.setUpdate_user(rs.getString("update_user"));
            goddess.setUpdate_date(rs.getDate("update_date"));
            goddess.setIsdel(rs.getInt("isdel"));
        }

        return goddess;
    }

    public List<Goddess> query(List<Map<String, Object>> params) throws SQLException {

        Connection connection = DBUtil.getConn();
        StringBuilder sb = new StringBuilder();
        sb.append("select t.* from imooc_goddess t where 1=1");
        if (params != null && params.size() > 0) {
            for (int i = 0; i < params.size(); i++) {
                Map<String, Object> map = params.get(i);
                sb.append(" and " + map.get("name") + " " + map.get("rela") + " " + map.get("value") + " ");
            }
        }

        PreparedStatement preparedStatement = connection.prepareStatement(sb.toString());
        System.out.println(sb.toString());
        ResultSet rs = preparedStatement.executeQuery();

        List<Goddess> goddessList = new ArrayList<Goddess>();
        Goddess goddess = null;
        while (rs.next()) {

            goddess = new Goddess();
            goddess.setUser_name(rs.getString("user_name"));
            goddess.setSex(rs.getInt("sex"));
            goddess.setAge(rs.getInt("age"));
            goddess.setBirthday(rs.getDate("birthday"));
            goddess.setEmail(rs.getString("email"));
            goddess.setMobile(rs.getString("mobile"));
            goddess.setCreate_user(rs.getString("create_user"));
            goddess.setCreate_date(rs.getDate("create_date"));
            goddess.setUpdate_user(rs.getString("update_user"));
            goddess.setUpdate_date(rs.getDate("update_date"));
            goddess.setIsdel(rs.getInt("isdel"));

            goddessList.add(goddess);
        }
        return goddessList;
    }
}
